﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Assesment.Library;
using Assesment.Library.DomainObject.Administration.Catalog;
using Assesment.Library.Enum;
using Assesment.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;
using Assesment.Library.DomainObject.Transaction;
using Training.Library.DomainObject.Administration.Catalog;

namespace Assesment.Library.DataAccess.Transaction
{
    public partial class AssesmentPositionRepo : IRepository<AssesmentPosition, int>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {

                        case SQL.Function.Aggregate.Count:
                            cmd.CommandText = "SP_AssesmentPosition_GridPage_CountData";
                            break;

                        default:
                            // do nothing 
                            break;
                    }


                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@column", SqlDbType.VarChar).Value = column;
                    cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = parameters;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<AssesmentPosition> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<AssesmentPosition> tt = new List<AssesmentPosition>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);


                    cmd.CommandText = "SP_AssesmentPosition_GridPage";
                    cmd.Parameters.Add("@startRow", SqlDbType.Int).Value = startRow;
                    cmd.Parameters.Add("@endRow", SqlDbType.Int).Value = endRow;
                    cmd.Parameters.Add("@sortName", SqlDbType.VarChar).Value = sortName;
                    cmd.Parameters.Add("@sortOrder", SqlDbType.VarChar).Value = sortOrder;
                    cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = parameters;
                    cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                AssesmentPosition t = new AssesmentPosition(true);
                                t.AssesmentPositionID = dr[AssesmentPosition.Fields.AssesmentPositionID] == DBNull.Value ? 0 : int.Parse(dr[AssesmentPosition.Fields.AssesmentPositionID].ToString());
                                t.PositionID = dr[AssesmentPosition.Fields.PositionID] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.PositionID].ToString());
                                t.DepartmentID = dr[AssesmentPosition.Fields.DepartmentID] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.DepartmentID].ToString());
                                t.NIK = dr[AssesmentPosition.Fields.NIK] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.NIK].ToString();
                                t.DepartmentIDAssesment = dr[AssesmentPosition.Fields.DepartmentIDAssesment] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.DepartmentIDAssesment].ToString());
                                t.PositionIDAssesment = dr[AssesmentPosition.Fields.PositionIDAssesment] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.PositionIDAssesment].ToString());
                                t.AssesmentDate = dr[AssesmentPosition.Fields.AssesmentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.AssesmentDate].ToString());
                                t.CreatedDate = dr[AssesmentPosition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[AssesmentPosition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[AssesmentPosition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[AssesmentPosition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.UpdatedBy].ToString();
                                t.JabatanAssesment = dr[AssesmentPosition.Fields.JabatanAssesment] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.JabatanAssesment].ToString();

                                t.Karyawan.NamaKaryawan = dr[Karyawan.Fields.NamaKaryawan] == DBNull.Value ? string.Empty : dr[Karyawan.Fields.NamaKaryawan].ToString();
                                t.Position.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.Location.Name = dr[Location.Fields.Name] == DBNull.Value ? string.Empty : dr[Location.Fields.Name].ToString();

                                t.Cabang.NamaCabang = dr[Cabang.Fields.NamaCabang] == DBNull.Value ? string.Empty : dr[Cabang.Fields.NamaCabang].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(AssesmentPosition domain, List<AssesmentPositionDetail> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Insert Data AssesmentPosition


                    SqlCommand cmd = new SqlCommand("SP_AssesmentPosition_Insert", conn);
                    //cmd.CommandText = "SP_AssesmentPosition_Insert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = (object)domain.PositionID ?? DBNull.Value;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = (object)domain.DepartmentID ?? DBNull.Value;
                    cmd.Parameters.Add("@NIK", SqlDbType.VarChar).Value = (object)domain.NIK ?? DBNull.Value;
                    cmd.Parameters.Add("@DepartmentIDAssesment", SqlDbType.Int).Value = (object)domain.DepartmentIDAssesment ?? DBNull.Value;
                    cmd.Parameters.Add("@PositionIDAssesment", SqlDbType.Int).Value = (object)domain.PositionIDAssesment ?? DBNull.Value;
                    cmd.Parameters.Add("@AssesmentDate", SqlDbType.DateTime).Value = (object)domain.AssesmentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@TotalFitID", SqlDbType.Int).Value = (object)domain.TotalFitID ?? DBNull.Value;
                    cmd.Parameters.Add("@PersonalDescription", SqlDbType.Text).Value = (object)domain.PersonalDescription ?? DBNull.Value;
                    cmd.Parameters.Add("@PersonalityID", SqlDbType.Int).Value = (object)domain.PersonalityID ?? DBNull.Value;
                    cmd.Parameters.Add("@LeadershipID", SqlDbType.Int).Value = (object)domain.LeadershipID ?? DBNull.Value;
 
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Insert Data Level Competency


                    #region Ambil AssesmentPositionID


                    SqlCommand cmdAssesmentPosition = new SqlCommand("SP_AssesmentPosition_SelectBy_CreatedByCreatedDate", conn);
                    cmdAssesmentPosition.CommandType = CommandType.StoredProcedure;
                    cmdAssesmentPosition.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdAssesmentPosition.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                    cmdAssesmentPosition.Transaction = txn;
                    var AssesmentPositionID = cmdAssesmentPosition.ExecuteScalar();
                    int resultAssesmentPositionID = AssesmentPositionID == DBNull.Value ? 0 : int.Parse(AssesmentPositionID.ToString());
                    cmdAssesmentPosition.Dispose();

                    #endregion


                    foreach (AssesmentPositionDetail data in list)
                    {


                        SqlCommand cmdAssesmentPositionDetail = new SqlCommand("SP_AssesmentPositionDetail_Insert", conn);
                        cmdAssesmentPositionDetail.CommandType = CommandType.StoredProcedure;
                        cmdAssesmentPositionDetail.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = resultAssesmentPositionID;
                        cmdAssesmentPositionDetail.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = (object)data.CompetencyID ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@KeyBehaviourDetailID", SqlDbType.Int).Value = (object)data.KeyBehaviourDetailID ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@StatusKeyBehaviour", SqlDbType.Bit).Value = (object)data.StatusKeyBehaviour ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@Rating", SqlDbType.Int).Value = (object)data.Rating ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;


                        cmdAssesmentPositionDetail.Transaction = txn;
                        cmdAssesmentPositionDetail.ExecuteNonQuery();
                        cmdAssesmentPositionDetail.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool DeleteWithDetail(int key)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {


                    #region Delete Table KeyBehaviourDetail



                    SqlCommand cmdLevel = new SqlCommand("SP_AssesmentPositionDetail_Delete_ByAssesmentPositionID", conn);
                    cmdLevel.CommandType = CommandType.StoredProcedure;
                    cmdLevel.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = key;

                    cmdLevel.Transaction = txn;
                    cmdLevel.ExecuteNonQuery();
                    cmdLevel.Dispose();

                    #endregion

                    #region Delete Table KeyBehaviour


                    SqlCommand cmd = new SqlCommand("SP_AssesmentPosition_Delete", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = key;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool Update(AssesmentPosition domain, List<AssesmentPositionDetail> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Update Data AssesmentPosition

                    
                    SqlCommand cmd = new SqlCommand("SP_AssesmentPosition_Update", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = domain.AssesmentPositionID;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = (object)domain.PositionID ?? DBNull.Value;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = (object)domain.DepartmentID ?? DBNull.Value;
                    cmd.Parameters.Add("@NIK", SqlDbType.VarChar).Value = (object)domain.NIK ?? DBNull.Value;
                    cmd.Parameters.Add("@DepartmentIDAssesment", SqlDbType.Int).Value = (object)domain.DepartmentIDAssesment ?? DBNull.Value;
                    cmd.Parameters.Add("@PositionIDAssesment", SqlDbType.Int).Value = (object)domain.PositionIDAssesment ?? DBNull.Value;
                    cmd.Parameters.Add("@AssesmentDate", SqlDbType.DateTime).Value = (object)domain.AssesmentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@TotalFitID", SqlDbType.Int).Value = (object)domain.TotalFitID ?? DBNull.Value;
                    cmd.Parameters.Add("@PersonalDescription", SqlDbType.Text).Value = (object)domain.PersonalDescription ?? DBNull.Value;
                    cmd.Parameters.Add("@PersonalityID", SqlDbType.Int).Value = (object)domain.PersonalityID ?? DBNull.Value;
                    cmd.Parameters.Add("@LeadershipID", SqlDbType.Int).Value = (object)domain.LeadershipID ?? DBNull.Value;
 

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Update Data AssesmentPositionDetail





                    foreach (AssesmentPositionDetail data in list)
                    {

                        SqlCommand cmdAssesmentPositionDetail = new SqlCommand("SP_AssesmentPositionDetail_Update", conn);
                        cmdAssesmentPositionDetail.CommandType = CommandType.StoredProcedure;
                        //cmdAssesmentPositionDetail.Parameters.Add("@AssesmentPositionDtlID", SqlDbType.Int).Value = data.AssesmentPositionDtlID;
                        cmdAssesmentPositionDetail.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = (object)domain.AssesmentPositionID ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = (object)data.CompetencyID ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@KeyBehaviourDetailID", SqlDbType.Int).Value = (object)data.KeyBehaviourDetailID ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@StatusKeyBehaviour", SqlDbType.Bit).Value = (object)data.StatusKeyBehaviour ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@Rating", SqlDbType.Int).Value = (object)data.Rating ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmdAssesmentPositionDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;

                        cmdAssesmentPositionDetail.Transaction = txn;
                        cmdAssesmentPositionDetail.ExecuteNonQuery();
                        cmdAssesmentPositionDetail.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public AssesmentPosition GetByPositionIDAssesment_NIK(int PositionIDAssesment,string NIK)
        {
            AssesmentPosition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //cmd.CommandText = "SELECT AssesmentPositionID, PositionID, DepartmentID, NIK, DepartmentIDAssesment, PositionIDAssesment, AssesmentDate, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM transactions.AssesmentPosition WHERE AssesmentPositionID = @AssesmentPositionID";
                    //cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SP_AssesmentPosition_SelectByNIK_PositionIDAssesment";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PositionIDAssesment", SqlDbType.Int).Value = PositionIDAssesment;
                    cmd.Parameters.Add("@NIK", SqlDbType.VarChar).Value = NIK;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new AssesmentPosition();
                                t.AssesmentPositionID = dr[AssesmentPosition.Fields.AssesmentPositionID] == DBNull.Value ? 0 : int.Parse(dr[AssesmentPosition.Fields.AssesmentPositionID].ToString());
                                t.PositionID = dr[AssesmentPosition.Fields.PositionID] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.PositionID].ToString());
                                t.DepartmentID = dr[AssesmentPosition.Fields.DepartmentID] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.DepartmentID].ToString());
                                t.NIK = dr[AssesmentPosition.Fields.NIK] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.NIK].ToString();
                                t.DepartmentIDAssesment = dr[AssesmentPosition.Fields.DepartmentIDAssesment] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.DepartmentIDAssesment].ToString());
                                t.PositionIDAssesment = dr[AssesmentPosition.Fields.PositionIDAssesment] == DBNull.Value ? (int?)null : int.Parse(dr[AssesmentPosition.Fields.PositionIDAssesment].ToString());
                                t.AssesmentDate = dr[AssesmentPosition.Fields.AssesmentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.AssesmentDate].ToString());
                                t.CreatedDate = dr[AssesmentPosition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[AssesmentPosition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[AssesmentPosition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[AssesmentPosition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[AssesmentPosition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[AssesmentPosition.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public DataTable GetDataTableReport(int AssesmentPositionID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "SP_AssesmentPosition_ExportExcel";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = AssesmentPositionID;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }

        public DataTable GetDataTableReport_Detail(int AssesmentPositionID, string CompetncyDesc)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "SP_AssesmentPosition_ExportExcel_Detail";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AssesmentPositionID", SqlDbType.Int).Value = AssesmentPositionID;
                    cmd.Parameters.Add("@CompetncyDesc", SqlDbType.VarChar).Value = CompetncyDesc;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }

    }
}
